package com_briup;

import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JdbcTest {
    // 驱动
    String driver = "com.mysql.cj.jdbc.Driver";
    // url
    String url = "jdbc:mysql://localhost:3306/briup";
    // 用户名
    String username = "root";
    // 密码
    String password = "1234";

    // 创建表
    @Test
    public void createTable() {
        // 数据库连接对象
        Connection conn = null;
        // 数据库操作对象
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql = "CREATE TABLE t_customer ("
                    + "id INTEGER PRIMARY KEY,"
                    + "name VARCHAR(20) NOT NULL,"
                    + "age INT,"
                    + "birthday date,"
                    + "register_time datetime"
                    + ")";
            int rows = stmt.executeUpdate(sql);
            // 5. 处理结果
            System.out.println("返回结果rows：" + rows);
        } catch (Exception e) {
            // 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }

    // 插入
    @Test
    public void insertData() {

        try {
            // 注册驱动
            Class.forName(driver);
            // 获取连接
            Connection conn = DriverManager.getConnection(url, username, password);
            // 获取执行sql语句的对象
            Statement stmt = conn.createStatement();
            // 执行sql语句
            String sql = "insert into t_customer (id, name, age, birthday, register_time) values " +
                    "(1, '张三', 24, '2000-08-02', now()), " +
                    "(2, '李四', 21, '2003-08-09', now()), " +
                    "(3, '王五', 22, '2002-05-11', now())," +
                    "(4, 'lo', 29, '1995-05-27', now())," +
                    "(5, 'orm', 24, '2000-06-06', now())";

            int rows = stmt.executeUpdate(sql);
            System.out.println("插入了 " + rows + " 行数据");
            // 关闭资源
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 查询
    @Test
    public void selectTable() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Customer> customerList = new ArrayList<>();
        try {
            // 注册驱动
            Class.forName(driver);
            // 获取连接
            conn = DriverManager.getConnection(url, username, password);
            // 获取执行sql语句的对象
            stmt = conn.createStatement();
            // 执行sql语句
            String sql = "SELECT * FROM t_customer WHERE birthday < '2000-01-01'";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");
                Timestamp register_time = rs.getTimestamp("register_time");
                Customer customer =  new Customer(id, name, age, birthday, register_time);
                customerList.add(customer);
            }
            // 封装
            for (Customer customer : customerList) {
                System.out.println(customer);
            }
            // 关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭 ResultSet 和 Statement
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
class  Customer{
    private int id;
    private String name;
    private int age;
    private Date birthday;
    private Timestamp register_time;
    public Customer(int id, String name, int age, Date birthday, Timestamp register_time) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.register_time = register_time;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public Timestamp getRegister_time() {
        return register_time;
    }

    public void setRegister_time(Timestamp register_time) {
        this.register_time = register_time;
    }
    @Override
    public String toString() {
        return "Customer{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", birthday=" + birthday +
                ", register_time=" + register_time +
                '}';
    }
}
